import openpyxl
import pymysql

sql = "insert into employees values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"

# wb = openpyxl.load_workbook(filename=r"F:\PythonFile\Define\pythonProject1\mine\baidu1.xlsx")
# sheet = wb["人员管理"]
host = "localhost"
port = 3306
user = "root"
password = "root"
database = "baidu"

db = pymysql.connect(host=host, port=port, user=user, password=password, database=database)
cursor = db.cursor()
def insertbd(sql,prom):
    cursor.executemany(sql, prom)
    db.commit()


# def readbad():
#
#     for row in sheet.iter_rows(min_row=2):
#         data = []
#         for cell in row:
#             data.append(cell.value)
#         insertbd(sql,data)

# readbad()





wb = openpyxl.load_workbook(filename=r"F:\PythonFile\Define\pythonProject1\mine\baidu1.xlsx")

st = wb[wb.sheetnames[0]]

insert_sql = "insert into employees  values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"

datas = []
count = 0
print(st.max_row)

for row  in st.iter_rows(min_row=2):
    data = []

    for cell in row:
        data.append(cell.value)
    count += 1

    if len(datas) == 5000 or st.max_row  ==  count:
        insertbd(insert_sql,datas)
        print("执行",count,"!")
        datas.clear()
    else:
        datas.append(data)


cursor.close()
db.close()




